import sys
import datetime


class PeopleInSubGroup:
    def __init__(self, SQLConn, dBName = "PEOPLE_IN_SUBGROUP"):
        self.SQLConn = SQLConn
        self.PeopleInSubGroupdBName = dBName
        
    def AddPersonToSubGroup(self, Person_ID, EventID, SubGroupID, Proportion):
        self.SQLConn.SendCommand("""
         INSERT INTO %s (Eve_id, Peo_id, SubG_id, Prop, Accept)
         VALUES ('%d', '%d', '%d', '%f', 0)
        """ %(self.PeopleInSubGroupdBName, EventID, Person_ID, SubGroupID, Proportion))
    
    def UpdatePerson(self, PID, EID, SubG_id, Prop):
         self.SQLConn.SendCommand("""UPDATE %s
                                SET Prop = '%d',
                                WHERE SubG_id = '%d' AND EID = '%d' AND PID = '%d'
                                """ % (self.PeopleInSubGroupdBName, Prop, SubG_id, EID, PID))
    
    def RemovePersonFromSubGroup(self, EID, PID, SubGroupID):
        self.SQLConn.SendCommand("""    
         DELETE FROM %s where SubG_id=%d AND Eve_id=%d AND Peo_id=%d
         """ % (self.PeopleInSubGroupdBName, SubGroupID, EID,PID))
        
    def RemovePerson(self, PID):
        self.SQLConn.SendCommand("""    
         DELETE FROM %s where Peo_id=%d
         """ % (self.PeopleInSubGroupdBName, PID))
        
    def RemoveEvent(self, EID):
        self.SQLConn.SendCommand("""    
         DELETE FROM %s where Eve_id=%d
         """ % (self.PeopleInSubGroupdBName, EID))
    
    def RemovePersonFromEvent(self, EID, PID):
        self.SQLConn.SendCommand("""    
         DELETE FROM %s where Eve_id=%d AND Peo_id = %d
         """ % (self.PeopleInSubGroupdBName, EID, PID))
        
    def RemoveSubGroup(self, SubG_id):
        self.SQLConn.SendCommand("""    
         DELETE FROM %s where SubG_id=%d
         """ % (self.PeopleInSubGroupdBName, SubG_id))
        
    def GetAllPeopleInSubgroupOnEvent(self, SubG_id, EID):
        self.SQLConn.SendCommand("""SELECT Peo_id, Prop FROM %s WHERE SubG_id = %d AND Eve_id = %d"""%(self.PeopleInSubGroupdBName, SubG_id, EID))
        rows = self.SQLConn.FetchAll ()
        People = []
        for Person in rows:
            People.append([Person[0], float(Person[1])])
        return People
    
    def GetAcceptState(self, PID, EID):
        self.SQLConn.SendCommand("""SELECT SubG_id, Accept FROM %s WHERE Peo_id = %d AND Eve_id = %d"""%(self.PeopleInSubGroupdBName, PID, EID))
        rows = self.SQLConn.FetchAll ()
        res = {}
        for SG in rows:
            res[SG[0]] = SG[1]
        return res
    
    def SetAcceptState(self, PID, SGID, Accept):
        self.SQLConn.SendCommand("""UPDATE %s
                                SET Accept = '%d'
                                WHERE SubG_id = %d AND Peo_id = %d
                                """ % (self.PeopleInSubGroupdBName, Accept, SGID, PID))
        
    def SubGroupExpenseUpdateStatus(self, SGID):
        self.SQLConn.SendCommand("""UPDATE %s
                                SET Accept = '0'
                                WHERE SubG_id = %d
                                """ % (self.PeopleInSubGroupdBName, SGID))
        
    
    
    def GetAllSubGroupsPersonInOnEvent(self, PID, EID):
        self.SQLConn.SendCommand("""SELECT SubG_id, Prop FROM %s WHERE Peo_id = %d AND Eve_id = %d"""%(self.PeopleInSubGroupdBName, PID, EID))
        rows = self.SQLConn.FetchAll ()
        Expenses = []
        for Exp in rows:
            Expenses.append([Exp[0], Exp[1]])
        return Expenses
    
    def GetSubGroupExpensePropDict(self, EID):
        self.SQLConn.SendCommand("""SELECT Peo_id, SubG_id, Prop FROM %s WHERE Eve_id = %d"""%(self.PeopleInSubGroupdBName, EID))
        rows = self.SQLConn.FetchAll ()
        Expenses = {}
        for Exp in rows:
            if Exp[0] not in Expenses.keys():
                Expenses[Exp[0]] = []
            Expenses[Exp[0]].append(Exp[2])
        return Expenses
       
    
def GenerateTables(SQL):
    print """Generating "PEOPLE_IN_SUBGROUP" Table"""
    SQL.SendCommand ("DROP TABLE IF EXISTS PEOPLE_IN_SUBGROUP")
    SQL.SendCommand("""CREATE TABLE PEOPLE_IN_SUBGROUP(
                    Eve_id int,
                    SubG_id int,
                    Peo_id int,
                    Prop float,
                    Accept int)
                    """)




    
        